{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "administrative-recycling",
   "metadata": {},
   "source": [
    "# 会议室超时计算\n",
    "\n",
    "- author: lhy\n",
    "- date: 2021.5.26\n",
    "- version: 0.0.1\n",
    "\n",
    "> 用于自动计算会议室在工余时间的使用时长。黄昌盛提出需求。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cellular-updating",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "96\n",
      "22\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "from xlrd import open_workbook, xldate_as_datetime, xldate_as_tuple\n",
    "import xlsxwriter\n",
    "import datetime\n",
    "import os\n",
    "\n",
    "\n",
    "filename = 'meet.xlsx'    # 读取的文件名\n",
    "\n",
    "\n",
    "'''\n",
    "格式化日期\n",
    "把所有以\"，\" \"。\" \"/\" \".\"分隔的日期更改为以\"-\"分隔\n",
    "返回datetime.datetime类型的日期变量\n",
    "'''\n",
    "def format_date(date):\n",
    "    fdate = date.replace('，', '-').replace('。', '-').replace('.', '-').replace('/', '-')\n",
    "    __date = datetime.datetime.strftime(fdate, '%Y-%m-%d')\n",
    "    return __date\n",
    "\n",
    "'''\n",
    "查询日期对应的星期\n",
    "# 这个功能暂时取消：@param date 日期，可以是字符串，年月日之间可用\"-\"或\"/\"连接\n",
    "@param date 日期，datetime类型\n",
    "@return 星期的数值, 星期的中文全称\n",
    "'''\n",
    "def get_week_day(date):\n",
    "    #__date = datetime.strptime(str(date).replacee('/', '-'), '%Y-%m-%d').date()\n",
    "    #__date = datetime.datetime.strptime(date.replace('/', '-'), '%Y-%m-%d')\n",
    "    week_day = {\n",
    "        0: '星期一',\n",
    "        1: '星期二',\n",
    "        2: '星期三',\n",
    "        3: '星期四',\n",
    "        4: '星期五',\n",
    "        5: '星期六',\n",
    "        6: '星期日'\n",
    "    }\n",
    "    day = date.weekday()\n",
    "    return day, week_day[day]\n",
    "\n",
    "'''\n",
    "定义工作时间\n",
    "@param date 日期，datetime类型\n",
    "@param start_time 开始时间，datetime类型\n",
    "@param end_time 结束时间，datetime类型\n",
    "@return ot 加班的时长，单位：分钟\n",
    "'''\n",
    "def over_time(date, meeting_start, meeting_end):\n",
    "    # 工作时间，元组里第一个是开始时间，第二个是结束时间\n",
    "    work_time = {\n",
    "        \"星期一\": (\n",
    "            ('8:00', '12:00'),\n",
    "            ('14:00', '17:00')\n",
    "        ),\n",
    "        \"星期二\": (\n",
    "            ('8:00', '12:00'),\n",
    "            ('14:00', '16:00')\n",
    "        ),\n",
    "        \"星期三\": (\n",
    "            ('8:00', '12:00'),\n",
    "            ('14:00', '16:00')\n",
    "        ),\n",
    "        \"星期四\": (\n",
    "            ('8:00', '12:00'),\n",
    "            ('14:00', '16:00')\n",
    "        ),\n",
    "        \"星期五\": (\n",
    "            ('8:00', '12:00'),\n",
    "            ('14:00', '16:00')\n",
    "        ),\n",
    "        \"星期六\": (\n",
    "        ),\n",
    "        \"星期日\": (\n",
    "        )\n",
    "    }\n",
    "    \n",
    "    week_day = get_week_day(date)\n",
    "    diff = 0\n",
    "    #print(\"日期是：%s， 星期：%s\" %(date, week_day[1]))\n",
    "    time_quantum = work_time[week_day[1]]\n",
    "    #print('时间段：{}, 开始：{}, 结束：{}'.format(time_quantum, meeting_start, meeting_end))\n",
    "\n",
    "    diff, start, end = sum_overtime(time_quantum, meeting_start, meeting_end)\n",
    "\n",
    "    #print('日期：{0}，会议开始：{1}， 会议结束：{2}， OT时长：{3}'.format(date, meeting_start, meeting_end, diff))\n",
    "    \n",
    "    return diff\n",
    "    \n",
    "'''\n",
    "计算开始时间至结束时间之间在工作时段外的总时长\n",
    "@param work_time 工作时间，以元组形式传输，分多段传入\n",
    "@param start_time 开始时间\n",
    "@param end_time 结束时间\n",
    "@return 返回总时长，单位分钟\n",
    "'''            \n",
    "def sum_overtime(work_quantum, meeting_start, meeting_end):\n",
    "    # 此函数为递归调用\n",
    "    ot = 0    # 加班时长（分钟）\n",
    "    \n",
    "    # 工作时间为空，即可返回现有的会议时间\n",
    "    if not work_quantum:\n",
    "        # datetime.timedelta只能获取秒或日，所以需要把秒除60，再四舍五入\n",
    "        return round((meeting_end - meeting_start).seconds/60), meeting_end, meeting_end\n",
    "    \n",
    "    # 把工作时间的第一项中两个值分别转换为工作开始和结束时间\n",
    "    work_start = datetime.datetime.strptime(work_quantum[0][0], '%H:%M')\n",
    "    work_end = datetime.datetime.strptime(work_quantum[0][1], '%H:%M')\n",
    "    \n",
    "    if meeting_start >= work_start:\n",
    "        if meeting_start >= work_end:\n",
    "            # 会议在工作时间段之后开始\n",
    "            # 使用递归调用下一个工作时段示加班时间\n",
    "            ot += sum_overtime(work_quantum[1: ], meeting_start, meeting_end)[0]\n",
    "        elif meeting_end > work_end:\n",
    "            # 会议时间由工作开始后至工作结束后\n",
    "            # 使用递归调用下一个工作时段示加班时间，时间由本工作时段结束时开始算\n",
    "            ot += sum_overtime(work_quantum[1: ], work_end, meeting_end)[0]\n",
    "        else:\n",
    "            # 会议完全在工作时段内\n",
    "            return 0, meeting_end, meeting_end\n",
    "    elif meeting_end >= work_start:\n",
    "        if meeting_end > work_end:\n",
    "            # 会议时间由工作开始前至工作结束后\n",
    "            # 先计算工作时段前的加班时间，再递归调用计算下一时段\n",
    "            ot += round((work_start - meeting_start).seconds/60) + sum_overtime(work_quantum[1: ], work_end, meeting_end)[0]\n",
    "        else:\n",
    "            # 会议时间由工作开始前至工作结束前\n",
    "            # 返回加班时间\n",
    "            return round((work_start - meeting_start).seconds/60), meeting_end, meeting_end\n",
    "    else:\n",
    "        # 会议完全在工作开始前\n",
    "        # 返回加班时间\n",
    "        return round((meeting_end - meeting_start).seconds/60), meeting_end, meeting_end\n",
    "\n",
    "    return ot, meeting_end, meeting_end\n",
    "    \n",
    "\n",
    "fname, exname = os.path.splitext(filename)    # 分开文件名和扩展名\n",
    "wr_filename = fname + datetime.datetime.today().strftime(\"-%Y%m%d %H%M%S\") + exname    # 写入的文件名\n",
    "\n",
    "wb = open_workbook(filename)    # 打开工作簿\n",
    "\n",
    "#sheet = wb.sheet_by_index(0)    # 获取第1个sheet\n",
    "\n",
    "wr_wb = xlsxwriter.Workbook(wr_filename)    # 生成excel文件\n",
    "format_date = wr_wb.add_format({'num_format': 'yyyy/mm/dd'})    # 添加日期格式\n",
    "format_time = wr_wb.add_format({'num_format': 'HH:MM'})    # 添加时间格式\n",
    "\n",
    "for sheet_name in wb.sheet_names():     # 获取excel文件所有sheet名\n",
    "    sheet = wb.sheet_by_name(sheet_name)   # 根据名称获取sheet对象\n",
    "    \n",
    "    #print(sheet_name)    # 打印工作表名\n",
    "    #print(sheet.nrows)    # 获取最大行数\n",
    "    \n",
    "    wr_ws = wr_wb.add_worksheet(sheet_name)    # 添加worksheet\n",
    "\n",
    "\n",
    "    for i in range(sheet.nrows):    # sheet.nrows：获取sheet有效行数\n",
    "        print('第%d行'%i)\n",
    "        ot = 0\n",
    "        \n",
    "        row_value = sheet.row_values(i)    # 读取excel第i行\n",
    "        wr_ws.write_row('A' + str(i + 1), row_value)    # 写入第i行数据，整行写入\n",
    "        \n",
    "        # 判断sheet的A列是数值型，即作为本列需计算加班的依据\n",
    "        if (sheet.cell(i, 3).value != '') and (sheet.cell_type(i, 0) == 2):    # cell_type == 2为数值类型\n",
    "            #if i > 65: print(row_value)\n",
    "            date = xldate_as_datetime(sheet.cell(i, 3).value, 0)    # 获取日期\n",
    "            start_time, end_time = str(sheet.cell(i,5).value).replace('--', '-').replace('—', '-').split('-', 1)    # 获取开始和结束时间\n",
    "            # 把字符串转换为datetime类型\n",
    "            start_time = datetime.datetime.strptime(start_time.replace('：', ':'), '%H:%M')\n",
    "            end_time = datetime.datetime.strptime(end_time.replace('：', ':'), '%H:%M')\n",
    "            # 计算本行加班时间\n",
    "            ot = over_time(date, start_time, end_time)\n",
    "            \n",
    "            wr_ws.write(i, sheet.ncols, ot)\n",
    "\n",
    "    wr_ws.set_column('D:D', None, format_date)    # 设置列格式为日期型\n",
    "    wr_ws.set_column('J:N', None, format_time)    # 设置列格式为时间型\n",
    "\n",
    "wr_wb.close()    # 关闭文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "random-simulation",
   "metadata": {},
   "outputs": [],
   "source": [
    "from xlrd import open_workbook, xldate_as_datetime, xldate_as_tuple\n",
    "import xlsxwriter\n",
    "import datetime\n",
    "import os\n",
    "\n",
    "\n",
    "filename = 'metting.xlsx'    # 读取的文件名\n",
    "\n",
    "wr_filename = fname + datetime.datetime.today().strftime(\"-%Y%m%d %H%M%S\") + exname    # 写入的文件名\n",
    "\n",
    "fname, exname = os.path.splitext(filename)    # 分开文件名和扩展名\n",
    "wb = open_workbook(filename)    # 打开工作簿\n",
    "\n",
    "wr_wb = xlsxwriter.Workbook(wr_filename)    # 生成excel文件\n",
    "format_date = wr_wb.add_format({'num_format': 'yyyy/mm/dd'})    # 添加日期格式\n",
    "format_time = wr_wb.add_format({'num_format': 'HH:MM'})    # 添加时间格式\n",
    "\n",
    "for sheet_name in wb.sheet_names():     # 获取excel文件所有sheet名\n",
    "    sheet = wb.sheet_by_name(sheet_name)   # 根据名称获取sheet对象\n",
    "    \n",
    "    print(sheet.nrows)    # 获取最大行数\n",
    "    \n",
    "    wr_ws = wr_wb.add_worksheet(sheet_name)    # 添加worksheet\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "lyric-disclaimer",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[<xlrd.sheet.Sheet object at 0xaf2dfd50>, <xlrd.sheet.Sheet object at 0xaf2dfed0>, <xlrd.sheet.Sheet object at 0xaf2dfd90>]\n",
      "3801 96\n",
      "3706 22\n",
      "Sheet2 4\n"
     ]
    }
   ],
   "source": [
    "from xlrd import open_workbook, xldate_as_datetime, xldate_as_tuple\n",
    "import xlsxwriter\n",
    "import datetime\n",
    "import os\n",
    "\n",
    "filename = 'metting.xlsx'    # 读取的文件名\n",
    "wb = open_workbook(filename)\n",
    "print(wb.sheets())\n",
    "for sheet_name in wb.sheet_names():\n",
    "    sheet = wb.sheet_by_name(sheet_name)\n",
    "\n",
    "    print(sheet_name, sheet.nrows)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
